﻿Imports System.Data.SqlClient
Imports System.Data

Partial Class callinfo
    Inherits System.Web.UI.Page
    Dim conn As New SqlConnection("Data Source=sql.bluetonemedia.com;Initial Catalog=BlueToneMediaDB;Persist Security Info=True;User ID=BTMWiz;Password=esquel4ma@gic")
    'Dim conn2 As New SqlConnection("Data Source=sql.bluetonemedia.com;Initial Catalog=BlueToneMediaDB;Persist Security Info=True;User ID=BTMWiz;Password=esquel4ma@gic")
    Dim Qry As String
    Dim Qry2 As String
    Dim Qry3 As String
    Dim dv As New DataView
    Dim dv2 As New DataView

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Dim phone As String

        phone = ""

        phone = Request.QueryString("number")

        Dim area, pre, ends As String

        area = ""
        pre = ""
        ends = ""

        area = Mid(phone, 3, 3)
        pre = Mid(phone, 6, 3)
        ends = Right(phone, 4)

        Qry = "SELECT * FROM  Companys WHERE Phone LIKE '%" & area & "%' AND Phone LIKE '%" & pre & "%' AND Phone LIKE '%" & ends & "%' ORDER BY id"
        conn.Open()
        Dim Sample As New DataSet
        Dim Sql As New SqlDataAdapter(Qry, conn)
        Sql.Fill(Sample)
        dv.Table = Sample.Tables(0)

        Dim myid As Integer
        Dim exist As Boolean


        If Sample.Tables(0).Rows().Count = 0 Then
            exist = False
        Else
            exist = True
            myid = Server.HtmlDecode(dv(0)("ID"))
        End If

        'conn.Close()

        Dim results As String

        If exist Then
            Qry2 = "SELECT *, con.Email as Expr1 FROM  Companys as comp, Contacts as con WHERE comp.ID = con.CompanyID and comp.ID = '" & myid & "'"
            'conn.Open()
            Dim Sample2 As New DataSet
            Dim Sql2 As New SqlDataAdapter(Qry2, conn)
            Sql2.Fill(Sample2)
            dv.Table = Sample2.Tables(0)

			If Sample2.Tables(0).Rows().Count = 0 Then
            	results = "Is in the company datatable, but not in the contacts table"
        	Else
            	Dim rating As String

			  	rating = ""
				results = ""
  
				Dim i As Integer
				Dim comments As String
	
				comments = ""
	
				Dim dates As String
	
				dates = ""
	
				Qry3 = "SELECT * FROM Comments WHERE companyID = '" & myid & "'"
				'conn2.Open()
				Dim Sample3 As New DataSet
				Dim Sql3 As New SqlDataAdapter(Qry3, conn)
				Sql3.Fill(Sample3)
				dv2.Table = Sample3.Tables(0)
	
	
				If Sample3.Tables(0).Rows().Count > 0 Then
	
					For i = 0 To Sample3.Tables(0).Rows().Count - 1
						If Not isdbnull(dv2(i)("Comment")) Then
							dates = Server.HtmlDecode(dv2(i)("DateCreated"))
							comments = comments & Environment.NewLine & Left(dates, 10) & " " & Server.HtmlDecode(dv2(i)("Comment"))
						End If
					Next i
	
				End If
				'conn2.Close()
	
				Select Case (Server.HtmlDecode(dv(0)("CustomerRating")))
					Case 0
						rating = "Processing"
					Case 1
						rating = "Hot"
					Case 2
						rating = "Medium"
					Case 3
						rating = "Cold"
					Case 4
						rating = "Unknown"
					Case 5
						rating = "BillingSold"
					Case 6
						rating = "Dead"
					Case 7
						rating = "Collections"
					Case 8
						rating = "Email"
				End Select
	
				results = results + "<b>Customer Rating:</b> " & rating & "<br/><b>Company:</b> " & Server.HtmlDecode(dv(0)("Name")) & "<br/><b>Contact Name:</b> " & Server.HtmlDecode(dv(0)("FName")) & " " & Server.HtmlDecode(dv(0)("LName")) & "<br/><b>Email:</b> " & Server.HtmlDecode(dv(0)("Expr1")) & "<br/><b>Website:</b> <a href='" & Server.HtmlDecode(dv(0)("Website")) & "'>" & Server.HtmlDecode(dv(0)("Website")) & "</a><br/><b class='left'>Next Contact:</b><form action='nextcontact.aspx'><input name='next' class='date' /><input type='submit' value='submit' class='left' /><input name='companyid' value='" & myid & "' type='hidden' /></form><br/><br/><b class='left'>Call Notes:</b> <form action='addcomment.aspx'><input name='companyid' value='" & myid & "' type='hidden' /><input name='comment' /><input name='siteid' value='" & Server.HtmlDecode(dv(0)("Siteid")) & "' type='hidden' /><input type='submit' value='submit' /></form><br/><b>Comment History:</b><br/><textarea rows='10' cols='40'>" & comments & "</textarea><br/><a href='http://www.bluetonemedia.com/admin/company/?id=" & Server.HtmlDecode(dv(0)("ID")) & "' target='_blank'>CRM</a>&nbsp;&nbsp;&nbsp;<a href='http://www.bluetonemedia.com/admin/btmtime/' target='_blank'>Log Call</a>"
	
				conn.Close()
			End If
			
        Else
            results = "None"
        End If

		conn.Close()
        Response.Write(results)
        Response.ContentType = "text/plain"
        Response.Flush()
        Response.End()

    End Sub
End Class
